package com.zany.util.file;


import java.beans.PropertyDescriptor;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.List;

import com.zany.util.json.BeanInfoUtil;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;


public class ExcelUtil {
	private static WritableCellFormat wcfFCt = null;
	private static WritableCellFormat wcfFC = null;
	private static WritableCellFormat wcfFC1 = null;
	private static WritableCellFormat wcfFC3 = null;
	
	private static WritableSheet init(WritableWorkbook wbook, String sheetName,int gzdLs)throws Exception{
		  //生成名为参数(sheetName)的工作表，参数0表示这是第一页
		  WritableSheet wsheet = wbook.createSheet(sheetName, 0); // sheet名称
		  // 设置excel标题
		  WritableFont wfontt = new WritableFont(WritableFont.ARIAL, 18,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);   
		  WritableFont wfont = new WritableFont(WritableFont.ARIAL, 9,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);   
		  WritableFont wfont2 = new jxl.write.WritableFont(WritableFont.ARIAL, 9,WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
		  WritableFont wfont3 = new jxl.write.WritableFont(WritableFont.ARIAL, 9,WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.RED);
		  
		  wcfFCt = new WritableCellFormat(wfontt);
		  wcfFC = new WritableCellFormat(wfont);
		  wcfFC1 = new WritableCellFormat(wfont2);
		  wcfFC3 = new WritableCellFormat(wfont3);
		  
		  wcfFC3.setBackground(jxl.format.Colour.RED);
		  // 设置单元格背景色为浅绿色
		  wcfFCt.setBorder(jxl.format.Border.BOTTOM,jxl.format.BorderLineStyle.THICK);
		  wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
		  wcfFC1.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
		  wcfFC3.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
		  
		  wsheet.setRowView(0 ,500 ,false);
		  wsheet.setRowView(1 ,100 ,false);
		  wsheet.mergeCells(0 ,0 ,gzdLs-1,0 );
		  wsheet.getSettings().setRightMargin( 0.5 );//设置页边距
		  wsheet.addCell(new Label(0, 0, sheetName, wcfFCt));
		  return wsheet;
	}
	/**
	 * 创建并输出EXCEL
	 * @param os    输出流
	 * @param sheetName SHEEL的名字
	 * @param header  头
	 * @param list  数据
	 * @param flag  是否每行都带头  true是，ＦＡＬＳＥ否
	 * @return
	 * @throws Exception
	 */

	public static boolean exportExcel(OutputStream os, String sheetName,String[][] header,List list) throws Exception {
		WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
		WritableSheet wsheet = ExcelUtil.init(wbook, sheetName, header.length);
		// 开始生成主体内容
		for(int h=0;h<header.length;h++){
			wsheet.addCell(new Label(h, 2,header[h][0], wcfFC));
		}
		int i = 3;//第几行		
		for(int listNO=0;listNO<list.size();listNO++){
			Object obj = list.get(listNO);
			PropertyDescriptor[] props = BeanInfoUtil.getPropertyDescriptors(obj);
			for (int beanNO = 0; beanNO < props.length; beanNO++) {
				if(BeanInfoUtil.rightName(props[beanNO])){
					Object value = props[beanNO].getReadMethod().invoke(obj,null);
					String val = "";
					if(value instanceof java.lang.Double){
						val = new BigDecimal(BeanInfoUtil.getReadMethod(props[beanNO]).invoke(obj,null)==null?"0":BeanInfoUtil.getReadMethod(props[beanNO]).invoke(obj,null).toString()).toString();
					}else{
						val = BeanInfoUtil.getReadMethod(props[beanNO]).invoke(obj,null)==null?"":BeanInfoUtil.getReadMethod(props[beanNO]).invoke(obj,null).toString();
					}
					String key = props[beanNO].getName();
					for(int headerNO=0; headerNO<header.length; headerNO++){
						if(header[headerNO][1].trim().equals(key.trim())){
							if(val==null||val==""){
								wsheet.addCell(new Label(headerNO, i,val.toString(),wcfFC1));
							}else{
								wsheet.addCell(new Label(headerNO, i,val,wcfFC1));
							}						
						}
					}
				}
			}
			i++;
		}
		 // 主体内容生成结束
		wbook.write(); // 写入文件
		wbook.close();
		os.flush();
		os.close();
		return true;
	}
	public static void main(String[] args){
		String str = "02|22";
		String a[] = str.split("\\|");
		for(int i=0;i<a.length;i++){
			System.out.println(a[i]);
		}
		
	}
	
//	 public static void main(String[] args){
//		 Workbook workbook = null;
//		 Sheet sheet =null;
//		 Cell cell = null;
//		  
//		 try {
//		 	workbook = Workbook.getWorkbook(new File("D:/12.xls"));
//		 } catch (Exception e) {
//			 try {
//				 throw new Exception("file to import not found!");
//			 } catch (Exception e1) {
//				 e1.printStackTrace();
//			 }
//		 }
//		
//		 sheet= workbook.getSheet(0);
//		 int columnCount=sheet.getColumns();
//		 int rowCount=sheet.getRows();
//		 for (int i = 0; i <rowCount; i++) {
//			 for (int j = 0; j <columnCount; j++) {//注意，这里的两个参数，第一个是表示列的，第二才表示行
//				 cell=sheet.getCell(j, i); //要根据单元格的类型分别做处理，否则格式化过的内容可能会不正确
//				 if(cell.getType()==CellType.NUMBER){
//		         System.out.print(((NumberCell)cell).getValue());
//				 }
//				 else if(cell.getType()==CellType.DATE){
//					 System.out.print(((DateCell)cell).getDate());
//				 }
//				 else{
//					 System.out.print(cell.getContents());
//				 }
//		     
//		     //System.out.print(cell.getContents());
//		     System.out.print("/t");
//			 }
//			 System.out.print("/n");
//		 }
//		  //关闭它，否则会有内存泄露
//		 workbook.close();
//	}
}
